#!/usr/bin/python
#-*-coding:utf-8-*-

import MySQLdb
import time 
import datetime
import calendar


def add_months(dt,months):
    month = dt.month - 1 + months
    year = dt.year + month / 12
    month = month % 12 + 1
    day = min(dt.day,calendar.monthrange(year,month)[1])
    return dt.replace(year=year, month=month, day=day)

#insert db 
def insert(pid,name,sdate,salary,yj,sj,bj):
	isql = "INSERT INTO df_summary(pid, name, month,salary, yj, sj,bj) VALUES ('%d', '%s', '%s', '%.2f', '%.2f' ,'%.2f' , '%.2f')" % \
       (pid,name,sdate,salary,yj,sj,bj)
	try:
   	# 执行sql语句
   		cursor.execute(isql)
   	# 提交到数据库执行
   		db.commit()
	except:
   	# Rollback in case there is any error
   		db.rollback()

#-----------------------
def cal(name,pid,salary,stime):
	yj=0
	if salary <= 3000 :
		yj=salary*0.005
	elif salary <= 5000 :
		yj=salary*0.01
	elif salary <=10000 :
		yj=salary*0.015
	else :
		yj=salary*0.01 

	sj=0;

	bj=yj-sj;
	print 
	insert(pid,name,stime.strftime("%Y-%m"),salary,yj,sj,bj)

#--------------------

# 打开数据库连接
db = MySQLdb.connect("ip","superadmin","AdminSuper","airplane" )

# 使用cursor()方法获取操作游标 
cursor = db.cursor() 

sql="SELECT * from df_salary"
sql ="select b.name,a.pid,a.salary,stime,etime from df_salary a, df_person b where a.pid=b.id and pid=37" 
#and a.pid=38

   # 执行SQL语句
cursor.execute(sql)
   # 获取所有记录列表
results = cursor.fetchall()
for row in results:
	name = row[0]
	pid = row[1]
	salary = row[2]
	stime = row[3] 
	etime = row[4]
	# 打印结果

	for i in range(1,1000):
		# inseret 
		cal(name,pid,salary,stime)
		stime=add_months(stime,1)
		print stime
		if stime >= etime :
			break;


# 关闭数据库连接
db.close()





